from numpy import array
import pandas as pd
import numpy as np
import cvxpy as cp



#测算接口主函数，通过读取数据库中的参数，计算出结果，并保存到数据库
tmpl_no = '202309_01'
cog_dest = '发电'
#读数据
data_meizhong = pd.read_excel('炼焦煤种表.xlsx')
data_meizhong.columns = data_meizhong.columns.str.upper()
data_jieguotongji = pd.read_excel('炼焦结果统计表.xlsx')
data_jieguotongji.columns = data_jieguotongji.columns.str.upper()
data_meizhi = pd.read_excel('炼焦煤质表.xlsx')
data_meizhi.columns = data_meizhi.columns.str.upper()
data_sourceprice = pd.read_excel('炼焦资源价格表.xlsx')
data_sourceprice.columns = data_sourceprice.columns.str.upper()
data_canshu = pd.read_excel('炼焦参数表.xlsx')
data_canshu.columns = data_canshu.columns.str.upper()

data_meizhong.drop(['REC_ID'], axis=1, inplace=True)
data_meizhong.drop(['REC_CREATE_TIME'], axis=1, inplace=True)
data_meizhong.drop(['REC_CREATOR'], axis=1, inplace=True)
data_meizhong.drop(['REC_REVISE_TIME'], axis=1, inplace=True)
data_meizhong.drop(['REC_REVISOR'], axis=1, inplace=True)
data_meizhong.drop(['TMPL_NO'], axis=1, inplace=True)
data_meizhong.drop(['DATA_TYPE'], axis=1, inplace=True)
data_jieguotongji.drop(['REC_ID'], axis=1, inplace=True)
data_jieguotongji.drop(['REC_CREATE_TIME'], axis=1, inplace=True)
data_jieguotongji.drop(['REC_CREATOR'], axis=1, inplace=True)
data_jieguotongji.drop(['REC_REVISE_TIME'], axis=1, inplace=True)
data_jieguotongji.drop(['REC_REVISOR'], axis=1, inplace=True)
data_jieguotongji.drop(['TMPL_NO'], axis=1, inplace=True)
data_jieguotongji.drop(['DATA_TYPE'], axis=1, inplace=True)
data_meizhi.drop(['REC_ID'], axis=1, inplace=True)
data_meizhi.drop(['REC_CREATE_TIME'], axis=1, inplace=True)
data_meizhi.drop(['REC_CREATOR'], axis=1, inplace=True)
data_meizhi.drop(['REC_REVISE_TIME'], axis=1, inplace=True)
data_meizhi.drop(['REC_REVISOR'], axis=1, inplace=True)
data_meizhi.drop(['TMPL_NO'], axis=1, inplace=True)
data_meizhi.drop(['DATA_TYPE'], axis=1, inplace=True)
data_sourceprice.drop(['REC_ID'], axis=1, inplace=True)
data_sourceprice.drop(['REC_CREATE_TIME'], axis=1, inplace=True)
data_sourceprice.drop(['REC_CREATOR'], axis=1, inplace=True)
data_sourceprice.drop(['REC_REVISE_TIME'], axis=1, inplace=True)
data_sourceprice.drop(['REC_REVISOR'], axis=1, inplace=True)
data_sourceprice.drop(['TMPL_NO'], axis=1, inplace=True)
data_sourceprice.drop(['DATA_TYPE'], axis=1, inplace=True)
data_canshu.drop(['REC_ID'], axis=1, inplace=True)
data_canshu.drop(['REC_CREATE_TIME'], axis=1, inplace=True)
data_canshu.drop(['REC_CREATOR'], axis=1, inplace=True)
data_canshu.drop(['REC_REVISE_TIME'], axis=1, inplace=True)
data_canshu.drop(['REC_REVISOR'], axis=1, inplace=True)
data_canshu.drop(['TMPL_NO'], axis=1, inplace=True)
data_canshu.drop(['DATA_TYPE'], axis=1, inplace=True)
data_sourceprice.drop(['INIT_INV'], axis=1, inplace=True)
data_sourceprice.drop(['INV_WT'], axis=1, inplace=True)
data_sourceprice.drop(['RESOURCE_WT'], axis=1, inplace=True)
data_sourceprice.drop(['MERGE_FLAG'], axis=1, inplace=True)
data_sourceprice.drop(['PLAN_WT'], axis=1, inplace=True)

chengjiaolv_constant = data_canshu.loc[0]['COKEYR_CONST']
cogfasheng_constant = data_canshu.loc[0]['COG_GEN_CONST']
cujiaolv = data_canshu.loc[0]['SINTER_CRSCOKE_RATIO']
yejinjiaolv = data_canshu.loc[0]['COKING_METCOKRAT']
waigoujiaofenlv = data_canshu.loc[0]['PCOKE_COKEPOWD_RATE']
cogfadian = data_canshu.loc[0]['ELECOUT']
fadianmeihao = data_canshu.loc[0]['COAL_USE']
jiaotanchanliang = data_canshu.loc[0]['COKE_OUTPUT']
price_waigoudian = data_canshu.loc[0]['POWOUTSRC_PRICE']
price_tianranqi = data_canshu.loc[0]['NG_PRICE']
price_fadianmei = data_canshu.loc[0]['THMCOAL_PRICE']
data_other1 = data_sourceprice[data_sourceprice['PROD_DSCR']=='外购焦炭']
data_other1 = data_other1.reset_index(drop=True)
price_waigoujiaotan = data_other1.loc[0]['UNIT_PRICE']
data_other2 = data_sourceprice[data_sourceprice['PROD_DSCR']=='烧结燃料']
data_other2 = data_other2.reset_index(drop=True)
price_shaojieranliao = data_other2.loc[0]['UNIT_PRICE']
data_other3 = data_sourceprice[data_sourceprice['PROD_DSCR']=='COG']
data_other3 = data_other3.reset_index(drop=True)
price_cog = data_other3.loc[0]['UNIT_PRICE']
data_sourceprice = data_sourceprice[data_sourceprice['VAR']!='其他参数']
data_sourceprice = data_sourceprice.reset_index(drop=True)

# price_cog = data_canshu.loc[0]['COG_PRICE']
data_year = data_jieguotongji[data_jieguotongji['SCHEME_NAME'] == '年度预算']
data_year = data_year.reset_index(drop=True)
year_var1 = data_year.loc[0]['COKING_PCC_RATIO']
year_var2 = data_year.loc[0]['COKING_FATCOAL_RATIO']
year_var3 = data_year.loc[0]['COKING_GFCOAL_RATIO']
year_var4 = data_year.loc[0]['COKING_GASCOAL_RATIO']
year_var5 = data_year.loc[0]['COKING_LEANCOAL_RATIO']
data_month = data_jieguotongji[data_jieguotongji['SCHEME_NAME'] == '月预算']
data_month = data_month.reset_index(drop=True)
month_var1 = data_month.loc[0]['COKING_PCC_RATIO']
month_var2 = data_month.loc[0]['COKING_FATCOAL_RATIO']
month_var3 = data_month.loc[0]['COKING_GFCOAL_RATIO']
month_var4 = data_month.loc[0]['COKING_GASCOAL_RATIO']
month_var5 = data_month.loc[0]['COKING_LEANCOAL_RATIO']
data_adjust = data_jieguotongji[data_jieguotongji['SCHEME_NAME'] == '调整方案']
data_adjust = data_adjust.reset_index(drop=True)
adjust_var1 = data_adjust.loc[0]['COKING_PCC_RATIO']
adjust_var2 = data_adjust.loc[0]['COKING_FATCOAL_RATIO']
adjust_var3 = data_adjust.loc[0]['COKING_GFCOAL_RATIO']
adjust_var4 = data_adjust.loc[0]['COKING_GASCOAL_RATIO']
adjust_var5 = data_adjust.loc[0]['COKING_LEANCOAL_RATIO']

data_meizhi = data_meizhi[['PROD_CODE', 'ASH', 'COKE_VM', 'S']]
data_sourceprice = data_sourceprice[['PROD_CODE', 'ACT_CONSUME', 'UNIT_PRICE']]
v = ['PROD_CODE']
df3 = pd.merge(data_meizhong, data_sourceprice, on=v, how='left')
v = ['PROD_CODE']
df4 = pd.merge(df3, data_meizhi, on=v, how='left')
df4['TOTAL_PRICE'] = df4['ACT_CONSUME'] * df4['UNIT_PRICE']
df4['TOTAL_ASH'] = df4['ACT_CONSUME'] * df4['ASH']
df4['TOTAL_COKE_VM'] = df4['ACT_CONSUME'] * df4['COKE_VM']
df4['TOTAL_S'] = df4['ACT_CONSUME'] * df4['S']

data_var1 = df4[df4['VAR'] == '主焦']
data_var1 = data_var1.reset_index(drop=True)
var1_sum_wt = data_var1['ACT_CONSUME'].sum()
var1_sum_price = data_var1['TOTAL_PRICE'].sum()
var1_sum_ash = data_var1['TOTAL_ASH'].sum()
var1_sum_coke_vm = data_var1['TOTAL_COKE_VM'].sum()
var1_sum_s = data_var1['TOTAL_S'].sum()
var1_unit_price = var1_sum_price/var1_sum_wt
var1_ash = var1_sum_ash/var1_sum_wt
var1_coke_vm = var1_sum_coke_vm/var1_sum_wt
var1_s = var1_sum_s/var1_sum_wt
data_var2 = df4[df4['VAR'] == '肥煤']
data_var2 = data_var2.reset_index(drop=True)
var2_sum_wt = data_var2['ACT_CONSUME'].sum()
var2_sum_price = data_var2['TOTAL_PRICE'].sum()
var2_sum_ash = data_var2['TOTAL_ASH'].sum()
var2_sum_coke_vm = data_var2['TOTAL_COKE_VM'].sum()
var2_sum_s = data_var2['TOTAL_S'].sum()
var2_unit_price = var2_sum_price/var2_sum_wt
var2_ash = var2_sum_ash/var2_sum_wt
var2_coke_vm = var2_sum_coke_vm/var2_sum_wt
var2_s = var2_sum_s/var2_sum_wt
data_var3 = df4[df4['VAR'] == '1/3焦']
data_var3 = data_var3.reset_index(drop=True)
var3_sum_wt = data_var3['ACT_CONSUME'].sum()
var3_sum_price = data_var3['TOTAL_PRICE'].sum()
var3_sum_ash = data_var3['TOTAL_ASH'].sum()
var3_sum_coke_vm = data_var3['TOTAL_COKE_VM'].sum()
var3_sum_s = data_var3['TOTAL_S'].sum()
var3_unit_price = var3_sum_price/var3_sum_wt
var3_ash = var3_sum_ash/var3_sum_wt
var3_coke_vm = var3_sum_coke_vm/var3_sum_wt
var3_s = var3_sum_s/var3_sum_wt
data_var4 = df4[df4['VAR'] == '气煤']
data_var4 = data_var4.reset_index(drop=True)
var4_sum_wt = data_var4['ACT_CONSUME'].sum()
var4_sum_price = data_var4['TOTAL_PRICE'].sum()
var4_sum_ash = data_var4['TOTAL_ASH'].sum()
var4_sum_coke_vm = data_var4['TOTAL_COKE_VM'].sum()
var4_sum_s = data_var4['TOTAL_S'].sum()
var4_unit_price = var4_sum_price/var4_sum_wt
var4_ash = var4_sum_ash/var4_sum_wt
var4_coke_vm = var4_sum_coke_vm/var4_sum_wt
var4_s = var4_sum_s/var4_sum_wt
data_var5 = df4[df4['VAR'] == '瘦煤']
data_var5 = data_var5.reset_index(drop=True)
var5_sum_wt = data_var5['ACT_CONSUME'].sum()
var5_sum_price = data_var5['TOTAL_PRICE'].sum()
var5_sum_ash = data_var5['TOTAL_ASH'].sum()
var5_sum_coke_vm = data_var5['TOTAL_COKE_VM'].sum()
var5_sum_s = data_var5['TOTAL_S'].sum()
var5_unit_price = var5_sum_price/var5_sum_wt
var5_ash = var5_sum_ash/var5_sum_wt
var5_coke_vm = var5_sum_coke_vm/var5_sum_wt
var5_s = var5_sum_s/var5_sum_wt

year_unit_price = year_var1 / 100 * var1_unit_price + year_var2 / 100 * var2_unit_price + year_var3 / 100 * var3_unit_price + year_var4 / 100 * var4_unit_price + year_var5 / 100 * var5_unit_price
year_ash = year_var1 / 100 * var1_ash + year_var2 / 100 * var2_ash + year_var3 / 100 * var3_ash +year_var4 / 100 * var4_ash + year_var5 / 100 * var5_ash
year_coke_vm = year_var1 / 100 * var1_coke_vm + year_var2 / 100 * var2_coke_vm + year_var3 / 100 * var3_coke_vm +year_var4 / 100 * var4_coke_vm + year_var5 / 100 * var5_coke_vm
year_s = year_var1 / 100 * var1_s + year_var2 / 100 * var2_s + year_var3 / 100 * var3_s +year_var4 / 100 * var4_s + year_var5 / 100 * var5_s

month_unit_price = month_var1 / 100 * var1_unit_price + month_var2 / 100 * var2_unit_price + month_var3 / 100 * var3_unit_price + month_var4 / 100 * var4_unit_price + month_var5 / 100 * var5_unit_price
month_ash = month_var1 / 100 * var1_ash + month_var2 / 100 * var2_ash + month_var3 / 100 * var3_ash + month_var4 / 100 * var4_ash + month_var5 / 100 * var5_ash
month_coke_vm = month_var1 / 100 * var1_coke_vm + month_var2 / 100 * var2_coke_vm + month_var3 / 100 * var3_coke_vm +month_var4 / 100 * var4_coke_vm + month_var5 / 100 * var5_coke_vm
month_s = month_var1 / 100 * var1_s + month_var2 / 100 * var2_s + month_var3 / 100 * var3_s + month_var4 / 100 * var4_s + month_var5 / 100 * var5_s

adjust_unit_price = adjust_var1 / 100 * var1_unit_price + adjust_var2 / 100 * var2_unit_price + adjust_var3 / 100 * var3_unit_price + adjust_var4 / 100 * var4_unit_price + adjust_var5 / 100 * var5_unit_price
adjust_ash = adjust_var1 / 100 * var1_ash + adjust_var2 / 100 * var2_ash + adjust_var3 / 100 * var3_ash + adjust_var4 / 100 * var4_ash + adjust_var5 / 100 * var5_ash
adjust_coke_vm = adjust_var1 / 100 * var1_coke_vm + adjust_var2 / 100 * var2_coke_vm + adjust_var3 / 100 * var3_coke_vm + adjust_var4 / 100 * var4_coke_vm + adjust_var5 / 100 * var5_coke_vm
adjust_s = adjust_var1 / 100 * var1_s + adjust_var2 / 100 * var2_s + adjust_var3 / 100 * var3_s +adjust_var4 / 100 * var4_s + adjust_var5 / 100 * var5_s

year_chengjiaolv = 97 - year_coke_vm * 5 / 6 + chengjiaolv_constant
month_chengjiaolv = 97 - month_coke_vm * 5 / 6 + chengjiaolv_constant
adjust_chengjiaolv = 97 - adjust_coke_vm * 5 / 6 + chengjiaolv_constant

ganmeiliang = jiaotanchanliang / month_chengjiaolv * 100
year_jiaotanchanliang = ganmeiliang * year_chengjiaolv / 100
month_jiaotanchanliang = jiaotanchanliang
adjust_jiaotanchanliang = ganmeiliang * adjust_chengjiaolv / 100
year_cogfasheng = (9.37 * year_coke_vm + 66.7) * ganmeiliang / 30 / 24 + cogfasheng_constant
month_cogfasheng = (9.37 * month_coke_vm + 66.7) * ganmeiliang / 30 / 24 + cogfasheng_constant
adjust_cogfasheng = (9.37 * adjust_coke_vm + 66.7) * ganmeiliang / 30 / 24 + cogfasheng_constant

# data_jieguotongji_out = data_jieguotongji[['SCHEME_NAME', 'COKING_PCC_RATIO', 'COKING_FATCOAL_RATIO', 'COKING_GFCOAL_RATIO', 'COKING_GASCOAL_RATIO', 'COKING_LEANCOAL_RATIO']]
data_jieguotongji_out = pd.DataFrame(columns=['SCHEME_NAME', 'COKING_PCC_RATIO', 'COKING_FATCOAL_RATIO', 'COKING_GFCOAL_RATIO', 'COKING_GASCOAL_RATIO', 'COKING_LEANCOAL_RATIO',
                                'UNIT_PRICE', 'ASH', 'COKE_VM', 'S',
                                'COKING_COKEYR', 'COKE_OUTPUT', 'COG_GEN'])
dict = {}
dict['SCHEME_NAME'] = '年度预算'
dict['COKING_PCC_RATIO'] = year_var1
dict['COKING_FATCOAL_RATIO'] = year_var2
dict['COKING_GFCOAL_RATIO'] = year_var3
dict['COKING_GASCOAL_RATIO'] = year_var4
dict['COKING_LEANCOAL_RATIO'] = year_var5
dict['UNIT_PRICE'] = year_unit_price
dict['ASH'] = year_ash
dict['COKE_VM'] = year_coke_vm
dict['S'] = year_s
dict['COKING_COKEYR'] = year_chengjiaolv
dict['COKE_OUTPUT'] = year_jiaotanchanliang
dict['COG_GEN'] = year_cogfasheng
new_row = pd.Series(dict)
data_jieguotongji_out = data_jieguotongji_out.append(new_row, ignore_index=True)

dict = {}
dict['SCHEME_NAME'] = '月预算'
dict['COKING_PCC_RATIO'] = month_var1
dict['COKING_FATCOAL_RATIO'] = month_var2
dict['COKING_GFCOAL_RATIO'] = month_var3
dict['COKING_GASCOAL_RATIO'] = month_var4
dict['COKING_LEANCOAL_RATIO'] = month_var5
dict['UNIT_PRICE'] = month_unit_price
dict['ASH'] = month_ash
dict['COKE_VM'] = month_coke_vm
dict['S'] = month_s
dict['COKING_COKEYR'] = month_chengjiaolv
dict['COKE_OUTPUT'] = month_jiaotanchanliang
dict['COG_GEN'] = month_cogfasheng
new_row = pd.Series(dict)
data_jieguotongji_out = data_jieguotongji_out.append(new_row, ignore_index=True)

dict = {}
dict['SCHEME_NAME'] = '调整方案'
dict['COKING_PCC_RATIO'] = adjust_var1
dict['COKING_FATCOAL_RATIO'] = adjust_var2
dict['COKING_GFCOAL_RATIO'] = adjust_var3
dict['COKING_GASCOAL_RATIO'] = adjust_var4
dict['COKING_LEANCOAL_RATIO'] = adjust_var5
dict['UNIT_PRICE'] = adjust_unit_price
dict['ASH'] = adjust_ash
dict['COKE_VM'] = adjust_coke_vm
dict['S'] = adjust_s
dict['COKING_COKEYR'] = adjust_chengjiaolv
dict['COKE_OUTPUT'] = adjust_jiaotanchanliang
dict['COG_GEN'] = adjust_cogfasheng
new_row = pd.Series(dict)
data_jieguotongji_out = data_jieguotongji_out.append(new_row, ignore_index=True)

delta_jiaotanchanliang = adjust_jiaotanchanliang - month_jiaotanchanliang
delta_culiaoliang = delta_jiaotanchanliang * cujiaolv
delta_waigoujiao = delta_jiaotanchanliang * yejinjiaolv / (1 - waigoujiaofenlv)
delta_cujiaocaigou = delta_waigoujiao * waigoujiaofenlv - delta_culiaoliang
delta_waigoujiao_cost = -delta_waigoujiao * price_waigoujiaotan
delta_cujiaocaigou_cost = delta_cujiaocaigou * price_shaojieranliao
delta_peihemei_cost = (adjust_unit_price - month_unit_price) * ganmeiliang
delta_COGfashengliang = (month_cogfasheng - adjust_cogfasheng) * 30 * 24
# COG_DEST焦炉煤气去向
# 发电；补充焦炉煤气

if cog_dest != '发电':
    delta_COG_cost = delta_COGfashengliang * price_cog
# elif cog_dest=='发电':
else:
    delta_COG_fadian = delta_COGfashengliang * cogfadian
    tianranqifadian = delta_COGfashengliang / 2
    fadianmeifadian = delta_COG_fadian * fadianmeihao / 100
    waigoudian = delta_COG_fadian
    # 都是delta_COGfashengliang的倍数，比较倍数取最小的
    tianranqi_coef = 0.5 * price_tianranqi
    fadianmei_coef = cogfadian * fadianmeihao / 100 * price_tianranqi / 10000
    waigoudian_coef = cogfadian * price_waigoudian
    delta_COG_cost1 = tianranqifadian * price_tianranqi
    delta_COG_cost2 = fadianmeifadian * price_fadianmei / 10000
    delta_COG_cost3 = waigoudian * price_waigoudian
    if delta_COGfashengliang >= 0:
        if tianranqi_coef <= fadianmei_coef and tianranqi_coef <= waigoudian_coef:
            delta_COG_cost = delta_COG_cost1
        elif fadianmei_coef <= tianranqi_coef and fadianmei_coef <= waigoudian_coef:
            delta_COG_cost = delta_COG_cost2
        elif waigoudian_coef <= tianranqi_coef and waigoudian_coef <= fadianmei_coef:
            delta_COG_cost = delta_COG_cost3
    else:
        if tianranqi_coef >= fadianmei_coef and tianranqi_coef >= waigoudian_coef:
            delta_COG_cost = delta_COG_cost1
        elif fadianmei_coef >= tianranqi_coef and fadianmei_coef >= waigoudian_coef:
            delta_COG_cost = delta_COG_cost2
        elif waigoudian_coef >= tianranqi_coef and waigoudian_coef >= fadianmei_coef:
            delta_COG_cost = delta_COG_cost3
total_cost = delta_peihemei_cost + delta_waigoujiao_cost + delta_cujiaocaigou_cost + delta_COG_cost

df_out2 = pd.DataFrame(columns=['SCHEME_NAME',
                                'COKE_OUTPUT','CRSCOKE_OUTPUT' ,'COKING_DRY_COAL_AMT','COG_GEN',
                                'OUTSCOKE_CONSUME','SINTER_FUEL_PUR_INCR','UNIT_PRICE','DEST',
                                'OUTSCOKE_COST','SINTER_FUEL_PUR_COST','COALBLD_COST','COG_IMPC_COST',
                                'IRONMAKE_COST_CHANGE','TOTAL_COST',
                                'NG_CONSUME','THMCOAL_CONSUME','EQ'])

if cog_dest != '发电':
    dict = {}
    dict['SCHEME_NAME'] = '调整方案对比月预算'
    dict['COKE_OUTPUT'] = delta_jiaotanchanliang
    dict['CRSCOKE_OUTPUT'] = delta_culiaoliang
    dict['COKING_DRY_COAL_AMT'] = ganmeiliang
    dict['COG_GEN'] = delta_COGfashengliang
    dict['OUTSCOKE_CONSUME'] = delta_waigoujiao
    dict['SINTER_FUEL_PUR_INCR'] = delta_cujiaocaigou
    dict['UNIT_PRICE'] = adjust_unit_price - month_unit_price
    dict['DSET'] = cog_dest
    dict['OUTSCOKE_COST'] = delta_waigoujiao_cost
    dict['SINTER_FUEL_PUR_COST'] = delta_cujiaocaigou_cost
    dict['COALBLD_COST'] = delta_peihemei_cost
    dict['COG_IMPC_COST'] = delta_COG_cost
    dict['IRONMAKE_COST_CHANGE'] = delta_peihemei_cost + delta_waigoujiao_cost + delta_cujiaocaigou_cost
    dict['TOTAL_COST'] = total_cost
    new_row = pd.Series(dict)
    df_out2 = df_out2.append(new_row, ignore_index=True)
else:
    delta_COG_fadian = delta_COGfashengliang * cogfadian
    tianranqifadian = delta_COGfashengliang / 2
    fadianmeifadian = delta_COG_fadian * fadianmeihao / 100
    waigoudian = delta_COG_fadian
    dict = {}
    dict['SCHEME_NAME'] = '调整方案对比月预算'
    dict['COKE_OUTPUT'] = delta_jiaotanchanliang
    dict['CRSCOKE_OUTPUT'] = delta_culiaoliang
    dict['COKING_DRY_COAL_AMT'] = ganmeiliang
    dict['COG_GEN'] = delta_COGfashengliang
    dict['OUTSCOKE_CONSUME'] = delta_waigoujiao
    dict['SINTER_FUEL_PUR_INCR'] = delta_cujiaocaigou
    dict['UNIT_PRICE'] = adjust_unit_price - month_unit_price
    dict['DEST'] = '发电最优'
    dict['OUTSCOKE_COST'] = delta_waigoujiao_cost
    dict['SINTER_FUEL_PUR_COST'] = delta_cujiaocaigou_cost
    dict['COALBLD_COST'] = delta_peihemei_cost
    dict['COG_IMPC_COST'] = delta_COG_cost
    dict['IRONMAKE_COST_CHANGE'] = delta_peihemei_cost + delta_waigoujiao_cost + delta_cujiaocaigou_cost
    dict['TOTAL_COST'] = total_cost
    new_row = pd.Series(dict)
    df_out2 = df_out2.append(new_row, ignore_index=True)
    dict = {}
    dict['SCHEME_NAME'] = '调整方案对比月预算'
    dict['COKE_OUTPUT'] = delta_jiaotanchanliang
    dict['CRSCOKE_OUTPUT'] = delta_culiaoliang
    dict['COKING_DRY_COAL_AMT'] = ganmeiliang
    dict['COG_GEN'] = delta_COGfashengliang
    dict['OUTSCOKE_CONSUME'] = delta_waigoujiao
    dict['SINTER_FUEL_PUR_INCR'] = delta_cujiaocaigou
    dict['UNIT_PRICE'] = adjust_unit_price - month_unit_price
    dict['DEST'] = '发电天然气代替'
    dict['OUTSCOKE_COST'] = delta_waigoujiao_cost
    dict['SINTER_FUEL_PUR_COST'] = delta_cujiaocaigou_cost
    dict['COALBLD_COST'] = delta_peihemei_cost
    dict['COG_IMPC_COST'] = tianranqifadian * price_tianranqi
    dict['IRONMAKE_COST_CHANGE'] = delta_peihemei_cost + delta_waigoujiao_cost + delta_cujiaocaigou_cost
    dict['TOTAL_COST'] = delta_peihemei_cost + delta_waigoujiao_cost + delta_cujiaocaigou_cost + tianranqifadian * price_tianranqi
    dict['NG_CONSUME'] = tianranqifadian
    new_row = pd.Series(dict)
    df_out2 = df_out2.append(new_row, ignore_index=True)
    dict = {}
    dict['SCHEME_NAME'] = '调整方案对比月预算'
    dict['COKE_OUTPUT'] = delta_jiaotanchanliang
    dict['CRSCOKE_OUTPUT'] = delta_culiaoliang
    dict['COKING_DRY_COAL_AMT'] = ganmeiliang
    dict['COG_GEN'] = delta_COGfashengliang
    dict['OUTSCOKE_CONSUME'] = delta_waigoujiao
    dict['SINTER_FUEL_PUR_INCR'] = delta_cujiaocaigou
    dict['UNIT_PRICE'] = adjust_unit_price - month_unit_price
    dict['DEST'] = '发电发电煤代替'
    dict['OUTSCOKE_COST'] = delta_waigoujiao_cost
    dict['SINTER_FUEL_PUR_COST'] = delta_cujiaocaigou_cost
    dict['COALBLD_COST'] = delta_peihemei_cost
    dict['COG_IMPC_COST'] = fadianmeifadian * price_fadianmei / 10000
    dict['IRONMAKE_COST_CHANGE'] = delta_peihemei_cost + delta_waigoujiao_cost + delta_cujiaocaigou_cost
    dict['TOTAL_COST'] = delta_peihemei_cost + delta_waigoujiao_cost + delta_cujiaocaigou_cost + fadianmeifadian * price_fadianmei / 10000
    dict['THMCOAL_CONSUME'] = fadianmeifadian
    new_row = pd.Series(dict)
    df_out2 = df_out2.append(new_row, ignore_index=True)
    dict = {}
    dict['SCHEME_NAME'] = '调整方案对比月预算'
    dict['COKE_OUTPUT'] = delta_jiaotanchanliang
    dict['CRSCOKE_OUTPUT'] = delta_culiaoliang
    dict['COKING_DRY_COAL_AMT'] = ganmeiliang
    dict['COG_GEN'] = delta_COGfashengliang
    dict['OUTSCOKE_CONSUME'] = delta_waigoujiao
    dict['SINTER_FUEL_PUR_INCR'] = delta_cujiaocaigou
    dict['UNIT_PRICE'] = adjust_unit_price - month_unit_price
    dict['DEST'] = '发电外购电代替'
    dict['OUTSCOKE_COST'] = delta_waigoujiao_cost
    dict['SINTER_FUEL_PUR_COST'] = delta_cujiaocaigou_cost
    dict['COALBLD_COST'] = delta_peihemei_cost
    dict['COG_IMPC_COST'] = waigoudian * price_waigoudian
    dict['IRONMAKE_COST_CHANGE'] = delta_peihemei_cost + delta_waigoujiao_cost + delta_cujiaocaigou_cost
    dict['TOTAL_COST'] = delta_peihemei_cost + delta_waigoujiao_cost + delta_cujiaocaigou_cost + waigoudian * price_waigoudian
    dict['EQ'] = waigoudian
    new_row = pd.Series(dict)
    df_out2 = df_out2.append(new_row, ignore_index=True)

data_jieguotongji_out['TMPL_NO']=tmpl_no
data_jieguotongji_out['DATA_TYPE']='CS'
writer = pd.ExcelWriter('炼焦结果统计表测算.xlsx')
data_jieguotongji_out.to_excel(writer, sheet_name='Sheet1', index=False)
writer.save()
df_out2['TMPL_NO']=tmpl_no
df_out2['DATA_TYPE']='CS'
writer = pd.ExcelWriter('炼焦方案对比表测算.xlsx')
df_out2.to_excel(writer, sheet_name='Sheet1', index=False)
writer.save()

print('finish')



